using System;
using System.Web;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using System.Collections.Specialized;
using System.Xml;
using System.IO;
using System.Threading;
using System.Diagnostics;
using System.Net;
using System.Drawing;
using System.Drawing.Imaging;
using System.Data;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Data.SQLite;
using System.Net;
using System.Net.NetworkInformation;

namespace JitEngine.TDriver
{

    public class Multiple
    {
        public static void Main(string[] args)
        {
            try
            {
                PingReply reply = new Ping().Send("127.0.0.1");
                if(reply.Status == IPStatus.Success)
                {
                    System.Console.WriteLine("Ping successfully.");
                }else{
                    System.Console.WriteLine("Ping failure.");
                }
                string cDataBase="";
                string cdbTable="";

                string cFILE_NAME="setting.ini";
                string _cmd="";
                string _SQL_FILE="";
                string OutputFile="";

                Arguments oArguments = new Arguments(args);
                System.Console.WriteLine("C  command");
                System.Console.WriteLine("P  Profile");
                System.Console.WriteLine("S  SQL");
                if (oArguments["C"] != null)
                {
                    _cmd = oArguments["C"];
                }

                if (oArguments["P"] != null)
                {
                    cFILE_NAME = oArguments["P"];
                }

                if (oArguments["S"] != null)
                {
                    _SQL_FILE = oArguments["S"];
                }

                string cAPP_PATH = AppDomain.CurrentDomain.BaseDirectory;

                string dbAdapter="";
                bool clearbef=false;
                bool compress=false;
                bool split   =false;
                bool dropifexist=false;
                bool createtable=false;
                string user_id="";
                string password="";
                string servername="";
                bool doubleconfirm=false;
                bool cTransaction=false;
                
                if (File.Exists(cFILE_NAME))
                {
                    StreamReader sr = new StreamReader(cFILE_NAME);

                    string s="";
                    string cName="";
                    string cValue="";
                    string cDATA_PATH="";
                    string TargetType="mssql";
                    string SourceType="mssql";

                    while ((s = sr.ReadLine()) != null)
                    {
                        if (s.IndexOf("=") > 0)
                        {
                            int cPositionB = s.IndexOf("=");

                            cName = s.Substring(0, cPositionB);
                            cValue = s.Substring(cPositionB + 1, s.Length - cPositionB - 1);
                            cValue=cValue.Trim();

                            //Console.WriteLine(cName);

                            if (cName.ToUpper()=="DATABASE")
                            {
                                cDataBase=cValue;
                            }else if (cName.ToUpper()=="USER_ID")
                            {

                                user_id=cValue;
                            }else if (cName.ToUpper()=="PASSWORD")
                            {

                                password=cValue;
                            }else if (cName.ToUpper()=="OUTPUTFILE")
                            {

                                OutputFile=cValue;
                            }else if (cName.ToUpper()=="SERVERNAME")
                            {

                                servername=cValue;
                            }else if (cName.ToUpper()=="DBSTR")
                            {

                                dbAdapter=cValue;
                            }else if (cName.ToUpper()=="TRANSACTION")
                            {
                                if (cValue=="Y" || cValue=="y")
                                {
                                    cTransaction=true;
                                }else{
                                    cTransaction=false;
                                }
                            }else if (cName.ToUpper()=="DATA_PATH")
                            {

                                cDATA_PATH=cValue;
                                cDATA_PATH=cDATA_PATH.Replace("{APP_PATH}",cAPP_PATH);
                                cDATA_PATH = cDATA_PATH.Replace("{MM}", DateTime.Now.ToString("MM"));
                                cDATA_PATH = cDATA_PATH.Replace("{YY}", DateTime.Now.ToString("yy"));
                                cDATA_PATH = cDATA_PATH.Replace("{YYYY}", DateTime.Now.ToString("yyyy"));
                                cDATA_PATH = cDATA_PATH.Replace("{DD}", DateTime.Now.ToString("dd"));
                                cDATA_PATH = cDATA_PATH.Replace("{HH}", DateTime.Now.ToString("HH"));
                                cDATA_PATH = cDATA_PATH.Replace("{HM}", DateTime.Now.Minute.ToString());
                                cDATA_PATH = cDATA_PATH.Replace("{YH}", (DateTime.Now.DayOfYear*24+DateTime.Now.Hour).ToString());
                                cDATA_PATH = cDATA_PATH.Replace("{YM}", (DateTime.Now.DayOfYear*24*60+DateTime.Now.Hour*60+DateTime.Now.Minute).ToString());
                            }else if (cName.ToUpper()=="DBTABLE")
                            {
                                cdbTable=cValue;
                            }else if (cName.ToUpper()=="TARGETTYPE")
                            {
                                TargetType=cValue;

                            }else if (cName.ToUpper()=="SOURCETYPE")
                            {
                                SourceType=cValue;

                            }else if (cName.ToUpper()=="DROPIFEXIST")
                            {
                                if (cValue=="Y" || cValue=="y")
                                {
                                    dropifexist=true;
                                }else{
                                    dropifexist=false;
                                }
                            }else if (cName.ToUpper()=="CREATETABLE")
                            {
                                if (cValue=="Y" || cValue=="y")
                                {
                                    createtable=true;
                                }else{
                                    createtable=false;
                                }
                            }else if (cName.ToUpper()=="COMPRESS")
                            {
                                if (cValue=="Y" || cValue=="y")
                                {
                                    compress=true;
                                }else{
                                    compress=false;
                                }

                            }else if (cName.ToUpper()=="DOUBLECONFIRM")
                            {
                                if (cValue=="Y" || cValue=="y")
                                {
                                    doubleconfirm=true;
                                }else{
                                    doubleconfirm=false;
                                }
                            }else if (cName.ToUpper()=="DBADAPTER")
                            {
                                dbAdapter=cValue;
                            }
                        }
                    }
                    sr.Close();
                    dbAdapter="server="+servername+";uid="+user_id+";pwd="+password+";database="+cDataBase+"";

                    Console.WriteLine("cDataBase=" + cDataBase);

                    //lbl_DUMP_TO
                    //lbl_RESTORY_DATA_DIR
                    //lbl_RESTORY_DATA_FROM
                    //lbl_PRESS_OK_TO_CONFIRM
                    //lbl_STOP_RESTORE

                    if (dbAdapter!=""  && cDataBase!="")
                    {

                        cAPP_PATH=cAPP_PATH+cDataBase+"_Dat";
                        if (cDATA_PATH!="")
                        {
                            cAPP_PATH=cDATA_PATH;
                        }
                        if (!Directory.Exists(cAPP_PATH))
                        {
                            Directory.CreateDirectory(cAPP_PATH);
                        }


                        SqlStatementGenerator.SqlScriptGenerator.Target=TargetType;
                        SqlStatementGenerator.SqlScriptGenerator.dropifexist=dropifexist;
                        SqlStatementGenerator.SqlScriptGenerator.createtable=createtable;
                        SqlStatementGenerator.SqlScriptGenerator.user_id=user_id;
                        SqlStatementGenerator.SqlScriptGenerator.password=password;
                        SqlStatementGenerator.SqlScriptGenerator.database=cDataBase;
                        SqlStatementGenerator.SqlScriptGenerator.servername=servername;
                        SqlStatementGenerator.SqlScriptGenerator.outputfile=OutputFile;

                        if (_cmd=="dump")
                        {
                            SqlConnection conn = new SqlConnection(dbAdapter);
                            conn.Open();
                            Console.WriteLine("dump to " + cAPP_PATH);
                            if (cdbTable=="db")
                            {
                                string cSQLString="SELECT name FROM "+cDataBase+".dbo.SysObjects WHERE (xtype = 'U') ORDER BY name";
                                SqlCommand cmd = new SqlCommand(cSQLString, conn);

                                SqlDataReader dataReader = cmd.ExecuteReader();
                                while (dataReader.Read())
                                {
                                    if (dataReader["name"]!=DBNull.Value)
                                    {
                                        string cTABLE_NAME=(string)dataReader["name"];
                                        if ("dtproperties"==cTABLE_NAME)
                                        {
                                        }else if (_SQL_FILE=="*")
                                        {
                                            SqlStatementGenerator.SqlScriptGenerator.GenerateSqlInserts(cAPP_PATH,dbAdapter,"SELECT * FROM "+cTABLE_NAME,cTABLE_NAME,clearbef,compress);
                                        }else if (_SQL_FILE.ToUpper()==cTABLE_NAME.ToUpper())
                                        {
                                            SqlStatementGenerator.SqlScriptGenerator.GenerateSqlInserts(cAPP_PATH,dbAdapter,"SELECT * FROM "+cTABLE_NAME,cTABLE_NAME,clearbef,compress);
                                        }
                                    }
                                }
                            }else if (cdbTable!="")
                            {
                                if (File.Exists(cdbTable))
                                {

                                    StreamReader sr2 = new StreamReader(cdbTable);
                                    string s2="";
                                    string cName2="";
                                    string cValue2="";

                                    while ((s2 = sr2.ReadLine()) != null)
                                    {
                                        if (s2.IndexOf("=") > 0)
                                        {
                                            int cPositionB = s2.IndexOf("=");

                                            cName2 = s2.Substring(0, cPositionB);
                                            cValue2 = s2.Substring(cPositionB + 1, s2.Length - cPositionB - 1);
                                            cName2=cName2.Trim();
                                            cValue2=cValue2.Trim();

                                            if (_SQL_FILE=="*")
                                            {
                                                Console.Write(cName2 + " ");
                                                SqlStatementGenerator.SqlScriptGenerator.GenerateSqlInserts(cAPP_PATH,dbAdapter,cValue2,cName2,clearbef,compress);
                                                Console.WriteLine("");
                                            }else if (_SQL_FILE.ToUpper()==cName2.ToUpper())
                                            {
                                                Console.Write(cName2 + " ");
                                                SqlStatementGenerator.SqlScriptGenerator.GenerateSqlInserts(cAPP_PATH,dbAdapter,cValue2,cName2,clearbef,compress);
                                                Console.WriteLine("");
                                            }
                                        }
                                    }
                                }
                            }
                        }else if (_cmd=="load"){

                            if (TargetType=="mysql")
                            {
                                dbAdapter="Server="+servername+";UserId="+user_id+";Password="+password+";Database="+cDataBase+""; 
                            }

                            if (TargetType=="sqlite")
                            {
                                dbAdapter="Data Source="+cDataBase;
                            }
                            Console.WriteLine(TargetType+" dbAdapter=" + dbAdapter);

                            if (doubleconfirm && _SQL_FILE=="*")
                            {
                                Console.WriteLine("please restore data directory");
                                string cDir = Console.ReadLine();

                                if (Directory.Exists(cDir))
                                {
                                    cAPP_PATH=cDir;
                                }else if (Directory.Exists(cAPP_PATH+"\\"+cDir))
                                {
                                    cAPP_PATH=cAPP_PATH+"\\"+cDir;
                                }

                                Console.WriteLine("restore data from ["+cAPP_PATH+"]");

                                Console.WriteLine("1-Press [OK] To Confirm ");

                                string cProtect = Console.ReadLine();
                                if (cProtect=="OK")
                                {
                                    Console.WriteLine("2-Press [OK] To Confirm");
                                    cProtect = Console.ReadLine();
                                    if (cProtect=="OK")
                                    {

                                    }else{
                                        Console.WriteLine("invalid input data,restore stop");
                                        return;
                                    }
                                }else{
                                    Console.WriteLine("invalid input data,restore stop");
                                    return;
                                }
                            }


                            Console.WriteLine("Active Transaction "+cTransaction);

                            Console.WriteLine("restore data from ["+cAPP_PATH+"]");
                            Console.WriteLine(cAPP_PATH+"\\"+_SQL_FILE);
                            List<string> ZZFILE = new List<string>();
                            int TOTAL_SQL_FILE=0;
                            int TOTAL_FILE_QTY=0;

                            if (_SQL_FILE=="*")
                            {
                                DirectoryInfo oDirectoryInfo=new DirectoryInfo(cAPP_PATH);

                                FileSystemInfo[] oXFiles = oDirectoryInfo.GetFileSystemInfos();
                                Dictionary<string, string> objXCaptionData = new Dictionary<string, string>();
                                for (int i = 0; i < oXFiles.Length; i++)
                                {
                                    FileInfo objFile = oXFiles[i] as FileInfo;
                                    bool Skip=false;

                                    TOTAL_SQL_FILE++;

                                    if (objFile!=null)
                                    {
                                        string cNAME=objFile.FullName;

                                        if (cNAME==null)
                                        {
                                            cNAME="";
                                        }

                                        cNAME=cNAME.ToLower();
                                        for (int FILE_NAME_NDX=0;FILE_NAME_NDX<100;FILE_NAME_NDX++)
                                        {
                                            if (objFile.FullName.ToLower().IndexOf("-"+String.Format("{0:0000}",FILE_NAME_NDX)+".sql")>0)
                                            {
                                                cNAME=cNAME.Replace("-"+String.Format("{0:0000}",FILE_NAME_NDX)+".sql",".sql");
                                            }
                                        }
                                        if (!objXCaptionData.ContainsKey(cNAME))
                                        { 
                                            ZZFILE.Add(cNAME);
                                        }
                                        objXCaptionData[cNAME]=cNAME;
                                    }
                                }
                            }else{
                                ZZFILE.Add(cAPP_PATH+"\\"+_SQL_FILE);
                            }
                            TOTAL_FILE_QTY=0;

                            foreach (string sFILE_NAME in ZZFILE)
                            {
                                try
                                {
                                    ExecuteQuery oExecuteQuery=new ExecuteQuery();

                                    oExecuteQuery.Transaction=cTransaction;
                                    oExecuteQuery.dbAdapter=dbAdapter;
                                    oExecuteQuery.TargetType=TargetType;
                                    oExecuteQuery.Open();


                                    long SQL=0;
                                    int  ERR=0;
                                    string cLF="";
                                    long   _row=0;

                                    int FILE_NOT_FOUND=0;
                                    Console.WriteLine(sFILE_NAME);
                                    Console.SetCursorPosition(4, Console.CursorTop);
                                    Console.Write("Queries "+string.Format("{0,-9}",SQL)+" Errors "+string.Format("{0,-9}",ERR)+" Queries File " +string.Format("{0,-10}",0) +" Lines");
                                    
                                    for (int FILE_NAME_NDX=0;FILE_NAME_NDX<1000 && FILE_NOT_FOUND<100 ;FILE_NAME_NDX++)
                                    {
                                        string cF=sFILE_NAME;
                                        if (FILE_NAME_NDX>0)
                                        {
                                            cF=sFILE_NAME.ToLower().Replace(".sql","-"+String.Format("{0:0000}",FILE_NAME_NDX)+".sql");
                                        }
                                        if (File.Exists(cF))
                                        {
                                            TOTAL_FILE_QTY++;
                                            string ss=cF.Replace(cAPP_PATH+"\\","");
                                            cLF=ss;
                                            StreamReader sr3 = new StreamReader(cF,Encoding.UTF8);
                                            string s3="";
                                            string cName3="";
                                            string cValue3="";
                                            long  NDX=0;
                                            string SQLString="";
                                            string LN_STATUS="";
                                            string cCOLUMNS="";
                                            bool Skip=false;
                                            bool _Compress=false;
                                            while ((s3 = sr3.ReadLine()) != null)
                                            {
                                             		s3=s3.Replace("\\","\\\\");;
                                            
                                                Skip=false;
                                                if (NDX<5)
                                                {
                                                    if (s3.Length>2)
                                                    {
                                                        if (s3.IndexOf(">=>=>")==0  && cCOLUMNS=="")
                                                        {
                                                            cCOLUMNS=s3.Replace(">=>=>","");;
                                                            Skip=true;
                                                            _Compress=true;
                                                        }else	if (s3.IndexOf("//") == 0)
                                                        {
                                                            Skip=true;
                                                        }
                                                    }
                                                }
                                                if (!Skip)
                                                {
                                                    if (_Compress)
                                                    {
                                                        if (s3==">=>=>" && cCOLUMNS!="")
                                                        {
                                                            s3=cCOLUMNS;
                                                        }
                                                    }

                                                    if ( ((LN_STATUS=="E" && (s3=="" || s3=="GO")) || (LN_STATUS=="F" && s3=="GO") ) && SQLString!="")
                                                    {
                                              					_row++;
                                                    	
                                                        try
                                                        {
                                                            oExecuteQuery.ExecuteNonQuery(SQLString);
                                                            SQL++;
                                                        }
                                                        catch (Exception e)
                                                        {
                                                            ERR++;
                                                            oExecuteQuery.Write(cF+" LN [" + NDX + "] " + ERR+" "+  e.Message);
                                                            oExecuteQuery.Write("\n\n"+SQLString+"\n\n");
                                                        }
                                                        SQLString="";
                                                        LN_STATUS="";
                                                    }else{
                                                        SQLString=SQLString+s3;
                                                        if (s3!="")
                                                        {
                                                            LN_STATUS="";
                                                            if (s3.Length>=3)
                                                            {
                                                                string result = s3.Substring(s3.Length-2, 2);
                                                                if (result==");")
                                                                {
                                                                    LN_STATUS="E";
                                                                }else if (s3.Substring(s3.Length-1,1)==";")
                                                                {
                                                                    LN_STATUS="F";
                                                                }
                                                            }
                                                        }
                                                    }
                                                }
                                                if (_row>=1000)
                        												{
	                                                Console.SetCursorPosition(4, Console.CursorTop);
	                                                Console.Write("Queries "+string.Format("{0,-9}",SQL)+" Errors "+string.Format("{0,-9}",ERR)+" Queries File " +string.Format("{0,-10}",NDX) +" Lines");
	                                                _row=0;
                                              	}
                                                NDX++;
                                            }
                                            sr3.Close();
				                                    Console.SetCursorPosition(4, Console.CursorTop);
				                                    Console.Write("Queries "+string.Format("{0,-9}",SQL)+" Errors "+string.Format("{0,-9}",ERR)+" Queries File " +string.Format("{0,-10}",NDX) +" Lines");

                                        }else{
                                            FILE_NOT_FOUND++;
                                        }
                                    }

                                    
                                    oExecuteQuery.Commit();

                                    Console.WriteLine();	
                                    Console.WriteLine("");
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("Exception=" + e.ToString());
                                }
                            }
                        }
                    }
                }else{
                    Console.WriteLine("profile file  not found");
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception=" + e.ToString());
            }
        }
    }

    public class ExecuteQuery
    {
        public string dbAdapter  ="";
        public bool   Transaction=false;
        public string TargetType="";
        private SqlConnection  msconn;
        private SqlTransaction mstrans=null;
        private MySqlConnection  myconn;
        private MySqlTransaction mytrans=null;

        private SQLiteConnection  sqliteconn;
        private SQLiteTransaction sqlitetrans=null;

        private int ii=0;
        public void Open()
        {
            if (TargetType=="mssql")
            {
                msconn = new SqlConnection(dbAdapter);
                msconn.Open();
                if (Transaction)
                {
                    mstrans = msconn.BeginTransaction();
                }
            }else if (TargetType=="mysql")
            {
                myconn = new MySqlConnection(dbAdapter);
                myconn.Open();
                if (Transaction)
                {
                	
                		this.ExecuteNonQuery("set unique_checks=0");
                		this.ExecuteNonQuery("set foreign_key_checks=0");
                		this.ExecuteNonQuery("set autocommit=0");
                    mytrans = myconn.BeginTransaction();
                    
                }
            }else if (TargetType=="sqlite")
            {
                sqliteconn = new SQLiteConnection(dbAdapter);
                sqliteconn.Open();
                if (Transaction)
                {
                    sqlitetrans = sqliteconn.BeginTransaction();
                }
            }
        }

        public void Write(string SQLString)
        {
            FileStream file = file = new FileStream("sql.log", FileMode.Append,FileAccess.Write);
            StreamWriter sw = new StreamWriter(file);
            sw.Write(SQLString);
            sw.Close();
            file.Close();

        }
        public void ExecuteNonQuery(string SQLString)
        {
            if (TargetType=="mssql")
            {
                SqlCommand cmd = new SqlCommand(SQLString, msconn);
                cmd.ExecuteNonQuery();
            }else if (TargetType=="mysql")
            {
                MySqlCommand cmd = new MySqlCommand(SQLString, myconn);
                cmd.ExecuteNonQuery();
            }else if (TargetType=="sqlite")
            {
                SQLiteCommand cmd = new SQLiteCommand(SQLString, sqliteconn);
                cmd.ExecuteNonQuery();
            }
        }

        public void Commit()
        {
            if (TargetType=="mssql")
            {
                if (Transaction && mstrans!=null)
                {
                    mstrans.Commit();
                }
                if (msconn!=null)
                {
                    msconn.Close();
                }
            }else if (TargetType=="mysql")
            {
                if (Transaction && mytrans!=null)
                {
                    mytrans.Commit();
                }

                if (myconn!=null)
                {
		            		this.ExecuteNonQuery("set unique_checks=1");
		            		this.ExecuteNonQuery("set foreign_key_checks=1");
		            		this.ExecuteNonQuery("set autocommit=1");
                    myconn.Close();
                }
                
            }else if (TargetType=="sqlite")
            {
                if (Transaction && sqlitetrans!=null)
                {
                    sqlitetrans.Commit();
                }
                if (sqliteconn!=null)
                {
                    sqliteconn.Close();
                }
            }
        }
    }
}
